#!/usr/bin/python3
import csv
import pymysql
import database.sqllit3

'''

解析csv文件并将去存储在mysql数据库中，如果表中已经存在该行，在先删除之后再做插入
'''


def parsecsv(filename):
    '''解析csv文件,提取文件中的日期，最高温度，最低温度三列'''
    date = []
    date1 = []
    with open(filename) as f:
        reader = csv.reader(f)
        csvlists = list(reader)
        for csvlist in csvlists:
            if csvlist == csvlists[0]:
                continue
            else:
                date.append(csvlist[0])
                date.append(csvlist[1])
                date.append(csvlist[3])
                tup = tuple(date)
                date1.append(tup)
                date = []
                tup = []
    return date1  # 返回一个列表，列表中每个元素都是一个元组，在插入数据时方便使用


def get_connect():
    '''获取mysql数据库连接'''
    # 四个参数依次是服务器地址，用户名，密码，数据库名称
    dbconn = pymysql.connect("localhost", "root", "123456", "test")
    return dbconn


def get_cursor(dbconn):
    '''该获取游标对形象'''
    if dbconn is not None:
        return dbconn.cursor()


def initdb(date):
    dbconn = get_connect()
    cu = get_cursor(dbconn)
    database.sqllit3.log('数据库连接成功')
    # mysql数据库占位符为%s
    save_sql = "INSERT INTO TemperatureF values (%s, %s, %s)"
    select_sql = "select count(*) from TemperatureF where date = %s"
    del_sql = "delete from TemperatureF where date = %s"
    for d in date:
        database.sqllit3.log('执行sql:[{}],参数:[{}]'.format(save_sql, d))
        # 如果表中已经存在此条数据，则先删除之后再插入
        key = d[0]
        cu.execute(select_sql, key)
        flag = cu.fetchone()  ##fetchone返回的数据为元组类型
        if flag[0] >= 1:
            cu.execute(del_sql, key)
        cu.execute(save_sql, d)
    dbconn.commit()
    dbconn.close()
    database.sqllit3.log('数据插入成功')


def main():
    filename = 'D:\Sofeware\eclipse-javaee\mygame\database\death_valley_2014.csv'
    date = parsecsv(filename)
    initdb(date)


if __name__ == '__main__':
    main()